package com.x.crm.dao;

import com.x.crm.model.*;
import com.x.crm.model.VO.IntentionVO;
import com.x.crm.util.IntentionQuery;
import com.x.framework.dao.BaseDao;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

@Repository
public class IntentionDao extends BaseDao {

//    private final static String baseQuerySql = "SELECT " +
//            "I.INTENTION_ID,I.CUSTOMER_ID,I.INTENTION_TYPE,I.INTENTION_STATE,I.INTENTION_BUDGET_MIN,I.INTENTION_BUDGET_MAX,I.INTENTION_RECHARGER,I.INTENTION_FOLLOWER,I.CAR_MILEAGE,I.CAR_COLOR,I.EXPECTED_PRICE_MIN,I.EXPECTED_PRICE_MAX,I.LICENSE_DATE,I.CEATE_TIME,I.UPDATE_TIME,I.RECHARGER_ID,I.FOLLOWER_ID,I.APPOINTMENT_TIME,I.INTENTION_REMARK,I.NEXT_FOLLOW_DATETIME,I.DEFEAT_TIME,I.LOSE_EFFICACY_TIME,I.FIXTURE_TIME,I.FIXTURE_CAR_NAME,I.FIXTURE_CAR_BID_PRICE,I.FIXTURE_EARNEST,I.FIXTURE_PRICE,I.FIXTURE_CAR_PIC,I.FIXTURE_VIN,I.REACH_COUNT, I.LAST_FOLLOW_DATE, I.LAST_FOLLOWER, I.LAST_FOLLOW_RESULT, I.MODEL_NAME, I.EARNEST, I.FIXTURE_CAR, "+
//            "C.CUSTOMER_NAME,C.CUSTOMER_PHONE,C.LEVEL_ID,C.PROVINCE_ID,C.CITY_ID,C.PROVINCE_NAME,C.CHANNEL_ID,C.COLLECTOR_ID,C.CUSTOMER_CREATE_TIME,C.CUSTOMER_UPDATE_TIME,C.CUSTOMER_GENDER,C.CITY_NAME,C.MARKET_ID,"+
//            "CHL.CHANNEL_NAME,CHL.CHANNEL_REMARK,CHL.CHANNEL_CREATE_TIME,CHL.CHANNEL_STATE," +
//            "LVL.LEVEL_TXT,LVL.LEVEL_CREATE_TIME,LVL.LEVEL_STATE" +
//            " FROM CRM_INTENTION I " +
//            "JOIN CRM_CUSTOMER C ON I.CUSTOMER_ID = C.CUSTOMER_ID " +
//            "LEFT JOIN CRM_CHANNEL CHL ON CHL.CHANNEL_ID = C.CHANNEL_ID " +
//            "LEFT JOIN CRM_CUSTOMER_LEVEL LVL ON LVL.LEVEL_ID = C.LEVEL_ID " +
//            "WHERE I.INTENTION_ID IS NOT NULL ";
//    private final static String baseQuerySql = "SELECT " +
//            "I.INTENTION_ID,I.INTENTION_TYPE,I.INTENTION_STATE,I.CAR_MILEAGE,I.CAR_COLOR,I.EXPECTED_PRICE_MIN,I.EXPECTED_PRICE_MAX,I.LICENSE_DATE,I.CEATE_TIME,I.UPDATE_TIME,I.FOLLOWER_ID,I.INTENTION_REMARK,I.NEXT_FOLLOW_DATETIME,I.FIXTURE_TIME,I.FIXTURE_CAR_NAME,I.FIXTURE_CAR_BID_PRICE,I.FIXTURE_EARNEST,I.FIXTURE_PRICE,I.FIXTURE_CAR_PIC,I.FIXTURE_VIN,I.REACH_COUNT, I.MODEL_NAME, I.EARNEST, I.FIXTURE_CAR, "+
//            "C.CUSTOMER_NAME,C.CUSTOMER_PHONE,C.LEVEL_ID,C.PROVINCE_ID,C.CITY_ID,C.PROVINCE_NAME,C.CHANNEL_ID,C.COLLECTOR_ID,C.CUSTOMER_CREATE_TIME,C.CUSTOMER_UPDATE_TIME,C.CUSTOMER_GENDER,C.CITY_NAME,C.MARKET_ID,"+
//            "CHL.CHANNEL_NAME,CHL.CHANNEL_REMARK,CHL.CHANNEL_CREATE_TIME,CHL.CHANNEL_STATE," +
//            "LVL.LEVEL_TXT,LVL.LEVEL_CREATE_TIME,LVL.LEVEL_STATE" +
//            " FROM CRM_INTENTION I " +
//            "JOIN CRM_CUSTOMER C ON I.CUSTOMER_ID = C.CUSTOMER_ID " +
//            "LEFT JOIN CRM_CHANNEL CHL ON CHL.CHANNEL_ID = C.CHANNEL_ID " +
//            "LEFT JOIN CRM_CUSTOMER_LEVEL LVL ON LVL.LEVEL_ID = C.LEVEL_ID " +
//            "WHERE I.INTENTION_ID IS NOT NULL ";
//    public List<Intention> selectIntentionList(IntentionQuery intentionQuery) throws Exception {
//        StringBuilder sb = new StringBuilder(baseQuerySql);
//        List<Object> params = new ArrayList<Object>();
//        searchCondition(intentionQuery, sb, params);
////        sb.append("ORDER BY I.NEXT_FOLLOW_DATETIME");
////        if (this.isNotNull(intentionQuery.getOrderColumn())){
////            sb.append("ORDER BY ? " );
////            params.add(intentionQuery.getOrderColumn());
////            if (this.isNotNull(intentionQuery.getOrderMode())){
////                sb.append(intentionQuery.getOrderMode());
////            }else{
////                sb.append("DESC");
////            }
////        }
////        else{
////
////        }
//        sb.append(" ORDER BY I.CEATE_TIME DESC");
//        return this.queryForList(sb.toString(),  Intention.class, params, intentionQuery);
//    }

//    private final static String baseCntSql = "SELECT COUNT(I.INTENTION_ID) FROM CRM_INTENTION I " +
//            "JOIN CRM_CUSTOMER C ON I.CUSTOMER_ID = C.CUSTOMER_ID " +
//            "WHERE I.INTENTION_ID IS NOT NULL ";
//    public int selectIntentionCnt(IntentionQuery intentionQuery) throws Exception {
//        StringBuilder sb = new StringBuilder(baseCntSql);
//        List<Object> params = new ArrayList<Object>();
//        searchCondition(intentionQuery, sb, params);
//        return this.queryForInt(sb.toString(), params);
//    }


	public int updateReleaseIntentionInfo(Intention intention) throws Exception {
		return this.execute("UPDATE CRM_INTENTION SET FOLLOWER_NAME = NULL, FOLLOWER_ID = NULL  WHERE  CUSTOMER_SHOP_ID = ? ", intention.getCustomerShopId());
	}

	private final static String baseIntentionNumSql = "SELECT COUNT(I.INTENTION_ID) FROM CRM_INTENTION I " +
			"WHERE I.INTENTION_ID IS NOT NULL ";

	public int selectIntentionNum(Intention intention) throws Exception {
		StringBuilder sb = new StringBuilder(baseIntentionNumSql);
		List<Object> params = new ArrayList<Object>();
		searchIntentionCondition(intention, sb, params);
		return this.queryForInt(sb.toString(), params);
	}


	private final static String baseIntentionInfoSql = "SELECT  *  FROM CRM_INTENTION I " +
			"WHERE I.INTENTION_ID IS NOT NULL ";

	public List<Intention> selectIntentionInfoList(Intention intention) throws Exception {
		StringBuilder sb = new StringBuilder(baseIntentionInfoSql);
		List<Object> params = new ArrayList<Object>();
		searchIntentionCondition(intention, sb, params);
		sb.append(" ORDER BY I.FIXTURE_TIME DESC");
		return this.queryForList(sb.toString(), Intention.class, params, intention);
	}


//	private final static String baseSelectOpportunitySql = "SELECT T1.INTENTION_TYPE, " +
//			"T1.INTENTION_ID," +
//			"T1.INTENTION_STATE," +
//			"T1.INTENTION_REMARK," +
//			"T1.CEATE_TIME," +
//			"T1.SHOP_ID," +
//			"T1.MARKET_ID," +
//			"T1.CHANNEL_NAME," +
//			"T1.CHANNEL_ID," +
//			"T1.HANDING_TIME," +
//			"T1.MODEL_NAME," +
//			"T1.CUSTOMER_MOBILE," +
//			"T2.CUSTOMER_SHOP_ID," +
//			"T2.FOLLOWER_NAME," +
//			"T2.FOLLOWER_ID," +
//			"T3.CUSTOMER_ID," +
// 			"T3.CUSTOMER_NAME," +
//			"T3.PROVINCE_NAME," +
//			"T3.CUSTOMER_NO," +
//			"T3.CITY_NAME," +
//			"T3.PROVINCE_ID," +
//			"T3.CITY_ID," +
//			"T3.CUSTOMER_PHONE," +
//			"T3.CUSTOMER_NICKNAME " +
//			"FROM CRM_INTENTION T1, CRM_CUSTOMER_SHOP T2, CRM_CUSTOMER T3 " +
//			"WHERE T1.CUSTOMER_SHOP_ID = T2.CUSTOMER_SHOP_ID " +
//			"AND T2.CUSTOMER_ID = T3.CUSTOMER_ID " ;
//
//
//	/**
//	 * 销售商机查询
//	 * @param intention
//	 * @return
//	 * @throws Exception
//	 */
//	public List<Intention> selectOpportunityList(Intention intention) throws Exception {
//		StringBuilder sb = new StringBuilder(baseSelectOpportunitySql);
//		List<Object> params = new ArrayList<Object>();
//		searchBasicCondition(intention, sb, params);
//		sb.append(" ORDER BY T1.CEATE_TIME ").append(" ");
//		if (this.isNotNull(intention.getIntentionVO()) &&  this.isNotNull(intention.getIntentionVO().getOrderMode())){
//			sb.append(intention.getIntentionVO().getOrderMode());
//		} else {
//			sb.append("DESC ");
//		}
//		return this.queryForList(sb.toString(), Intention.class, params, intention);
//	}


//	/**
//	 * 销售商机数量
//	 */
//	private final static String baseOpportunityNumSql = "SELECT COUNT(T1.INTENTION_ID) " +
//			"FROM CRM_INTENTION T1, CRM_CUSTOMER_SHOP T2, CRM_CUSTOMER T3 " +
//			"WHERE T1.CUSTOMER_SHOP_ID = T2.CUSTOMER_SHOP_ID " +
//			"AND T2.CUSTOMER_ID = T3.CUSTOMER_ID ";
//
//	public int selectOpportunityNum(Intention intention) throws Exception {
//		StringBuilder sb = new StringBuilder(baseOpportunityNumSql);
//		List<Object> params = new ArrayList<Object>();
//		searchBasicCondition(intention, sb, params);
//		return this.queryForInt(sb.toString(), params);
//	}


	private final static String baseGetCustomerIntentionListSql = "SELECT T1.*, " +
			"T2.CUSTOMER_NAME," +
			"T2.CITY_ID," +
			"T2.CITY_NAME," +
			"T2.PROVINCE_ID," +
			"T2.PROVINCE_NAME " +
			"FROM CRM_INTENTION T1, CRM_CUSTOMER_SHOP T2  " +
			"WHERE T1.CUSTOMER_SHOP_ID = T2.CUSTOMER_SHOP_ID ";


	/**
	 * 意向列表查询
	 *
	 * @param intention
	 * @return
	 * @throws Exception
	 */
	public List<Intention> getCustomerIntentionList(Intention intention) throws Exception {
		StringBuilder sb = new StringBuilder(baseGetCustomerIntentionListSql);
		List<Object> params = new ArrayList<Object>();
		searchBasicCondition(intention, sb, params);
		sb.append(" ORDER BY T1.CEATE_TIME ").append(" ");
		if (this.isNotNull(intention.getIntentionVO()) && this.isNotNull(intention.getIntentionVO().getOrderMode())) {
			sb.append(intention.getIntentionVO().getOrderMode());
		} else {
			sb.append("DESC ");
		}
		return this.queryForList(sb.toString(), Intention.class, params, intention);
	}

	private final static String baseGetCustomerIntentionNumSql = "SELECT COUNT(T1.INTENTION_ID) " +
			"FROM CRM_INTENTION T1, CRM_CUSTOMER_SHOP T2  " +
			"WHERE T1.CUSTOMER_SHOP_ID = T2.CUSTOMER_SHOP_ID ";

	/**
	 * 意向列表数量
	 *
	 * @param intention
	 * @return
	 * @throws Exception
	 */
	public int getCustomerIntentionNum(Intention intention) throws Exception {
		StringBuilder sb = new StringBuilder(baseGetCustomerIntentionNumSql);
		List<Object> params = new ArrayList<Object>();
		searchBasicCondition(intention, sb, params);
		return this.queryForInt(sb.toString(), params);
	}



	private final static String baseSelectIntentionFollowListSql = "SELECT  * FROM ( " +
			"SELECT " +
			"CIN.INTENTION_NO, " +
			"CIN.INTENTION_STATE, " +
			"CIN.FOLLOWER_ID, " +
			"CIN.FOLLOWER_NAME, " +
			"CIN.INTENTION_BUDGET_MIND, " +
			"CIN.INTENTION_BUDGET_MAXD, " +
			"CIN.GET_TIME, " +
			"CIN.PURPOSE, " +
			"CIN.PURCHASE_NATURE, " +
			"CIN.PURCHASE_MODE, " +
			"CIN.INTENTION_REMARK, " +
			"CIN.SHOP_ID, " +
			"CIN.MARKET_ID, " +
			"CIN.MODEL_NAME, " +
			"CIN.CUSTOMER_MOBILE, " +
			"CIN.CUSTOMER_CONTACT_NAME, " +
			"CIN.CUSTOMER_SHOP_ID, " +
			"CIN.INTENTION_CHANNEL_NAME, " +
			"CIN.INTENTION_LEVEL_TXT, " +
			"CIN.LAST_OPR_TIME, " +
			"CFU.* " +
			"FROM CRM_INTENTION AS CIN JOIN  *tableName*  AS CFU " +
			"ON CIN.INTENTION_ID = CFU.INTENTION_ID " +
			"ORDER BY *CREATE_TIME* DESC ) AS T1 " +
			"WHERE 1=1 ";

	/**
	 * 客户与客户被跟进表关联查询意向列表
	 * @param intention
	 * @param tableName
	 * @param createTime
	 * @return
	 * @throws Exception
	 */
	public List<Intention> selectIntentionFollowList(Intention intention,String tableName,String createTime) throws Exception {
		StringBuilder sb = new StringBuilder(baseSelectIntentionFollowListSql.replace("*tableName*", tableName).replace("*CREATE_TIME*", createTime));
		List<Object> params = new ArrayList<Object>();
		searchBasicCondition(intention, sb, params);
		sb.append(" GROUP BY T1.INTENTION_ID ");
		return this.queryForList(sb.toString(), Intention.class, params, intention);
	}



	private final static String baseSelectIntentionFollowNumSql = "SELECT COUNT(T1.INTENTION_ID) FROM ( " +
			"SELECT " +
			"CIN.INTENTION_NO, " +
			"CIN.INTENTION_STATE, " +
			"CIN.FOLLOWER_ID, " +
			"CIN.FOLLOWER_NAME, " +
			"CIN.INTENTION_BUDGET_MIND, " +
			"CIN.INTENTION_BUDGET_MAXD, " +
			"CIN.GET_TIME, " +
			"CIN.PURPOSE, " +
			"CIN.PURCHASE_NATURE, " +
			"CIN.PURCHASE_MODE, " +
			"CIN.INTENTION_REMARK, " +
			"CIN.SHOP_ID, " +
			"CIN.MARKET_ID, " +
			"CIN.MODEL_NAME, " +
			"CIN.CUSTOMER_MOBILE, " +
			"CIN.CUSTOMER_CONTACT_NAME, " +
			"CIN.CUSTOMER_SHOP_ID, " +
			"CIN.INTENTION_CHANNEL_NAME, " +
			"CIN.INTENTION_LEVEL_TXT, " +
			"CIN.LAST_OPR_TIME, " +
			"CFU.* " +
			"FROM CRM_INTENTION AS CIN JOIN  *tableName*  AS CFU " +
			"ON CIN.INTENTION_ID = CFU.INTENTION_ID " +
			"ORDER BY *CREATE_TIME* DESC ) AS T1 " +
			"WHERE 1=1 ";

	public int selectIntentionFollowNum(Intention intention,String tableName,String createTime) throws Exception {
		StringBuilder sb = new StringBuilder(baseSelectIntentionFollowNumSql.replace("*tableName*", tableName).replace("*CREATE_TIME*", createTime));
		List<Object> params = new ArrayList<Object>();
		searchBasicCondition(intention, sb, params);
		sb.append(" GROUP BY T1.INTENTION_ID ");
		return this.queryForInt(sb.toString(), params );
	}



	private final static String baseGetCustomerIntentionByIdSql = "SELECT T1.*, " +
			"T2.CUSTOMER_NAME," +
			"T2.CUSTOMER_CARD," +
			"T2.SEX," +
			"T2.BIRTHDAY," +
			"T2.CUSTOMER_ADDRESS," +
			"T2.AGE," +
			"T2.CHANNEL_ID," +
			"T2.CHANNEL_NAME," +
			"T2.PROVINCE_NAME," +
			"T2.PROVINCE_ID," +
			"T2.CUSTOMER_CARD," +
			"T2.CITY_ID," +
			"T2.CITY_NAME," +
			"T2.LEVEL_TXT," +
			"T2.LEVEL_ID," +
			"T2.FOLLOWER_NAME," +
			"T2.FOLLOWER_ID," +
			"T3.CUSTOMER_PHONE " +
			"FROM CRM_INTENTION T1, CRM_CUSTOMER_SHOP T2, CRM_CUSTOMER T3 " +
			"WHERE T1.CUSTOMER_SHOP_ID = T2.CUSTOMER_SHOP_ID " +
			"AND T2.CUSTOMER_ID = T3.CUSTOMER_ID ";

	public Intention getCustomerIntentionById(Intention intention) throws Exception {
		StringBuilder sb = new StringBuilder(baseGetCustomerIntentionByIdSql);
		List<Object> params = new ArrayList<>();
		searchBasicCondition(intention, sb, params);
		List<Intention> intentions = this.queryForList(sb.toString(), Intention.class, params);
		if (intentions != null && !intentions.isEmpty())
			return intentions.get(0);
		return null;
	}


	public void searchBasicCondition(Intention intention, StringBuilder sb, List<Object> params) {
		if (intention.getIsOpportunity() != null) {
			sb.append("AND T1.IS_OPPORTUNITY = ? ");
			params.add(intention.getIsOpportunity());
		}
		if (this.isString(intention.getCustomerMobile())) {
			sb.append("AND T1.CUSTOMER_MOBILE = ? ");
			params.add(intention.getCustomerMobile());
		}
		if (this.isString(intention.getCustomerContactName())) {
			sb.append("AND T1.CUSTOMER_CONTACT_NAME = ? ");
			params.add(intention.getCustomerContactName());
		}
		if (intention.getCustomerShop() != null && intention.getCustomerShop().getFollowState() != null) {
			sb.append("AND T2.FOLLOW_STATE = ? ");
			params.add(intention.getCustomerShop().getFollowState());
		}
		if (this.isString(intention.getFollowerId())) {
			sb.append("AND T1.FOLLOWER_ID = ? ");
			params.add(intention.getFollowerId());
		}
		if (this.isString(intention.getShopId())) {
			sb.append("AND T1.SHOP_ID = ? ");
			params.add(intention.getShopId());
		}
		if (this.isString(intention.getMarketId())) {
			sb.append("AND T1.MARKET_ID = ? ");
			params.add(intention.getMarketId());
		}
		if (this.isString(intention.getIntentionChannelId())) {
			sb.append("AND T1.INTENTION_CHANNEL_ID = ? ");
			params.add(intention.getIntentionChannelId());
		}
		if (this.isString(intention.getIntentionLevelId())) {
			sb.append("AND T1.INTENTION_LEVEL_ID = ? ");
			params.add(intention.getIntentionLevelId());
		}
		if (this.isString(intention.getIntentionId())) {
			sb.append("AND T1.INTENTION_ID = ? ");
			params.add(intention.getIntentionId());
		}
		if (intention.getIntentionState() != null) {
			sb.append("AND T1.INTENTION_STATE = ? ");
			params.add(intention.getIntentionState());
		}
		if (intention.getOpportunityState() != null) {
			sb.append("AND T1.OPPORTUNITY_STATE = ? ");
			params.add(intention.getOpportunityState());
		}
		if (intention.getIntentionVO() != null) {
			IntentionVO intentionVO = intention.getIntentionVO();
			if (intentionVO.getBrandId() != null || intentionVO.getSeriesId() != null) {
				StringBuilder childsql = new StringBuilder("SELECT CIM.INTENTION_ID FROM CRM_INTENTION_MODEL CIM WHERE 1=1 ");
				if (intentionVO.getBrandId() != null) {
					childsql.append("AND CIM.BRAND_ID = ? ");
					params.add(intentionVO.getBrandId());
				}
				if (intentionVO.getSeriesId() != null) {
					childsql.append("AND CIM.SERIES_ID = ? ");
					params.add(intentionVO.getSeriesId());
				}
				sb.append("AND T1.INTENTION_ID IN (").append(childsql).append(") ");
			}
			if (intentionVO.getIntentionStartTime() != null) {
				sb.append("AND T1.GET_TIME >= ? ");
				params.add(intentionVO.getIntentionStartTime());
			}
			if (intentionVO.getIntentionEndTime() != null) {
				sb.append("AND T1.GET_TIME <= ? ");
				params.add(intentionVO.getIntentionEndTime());
			}
			if (intentionVO.getIntentionOprType() != null) {
				if (intentionVO.getIntentionOprType() == 0) {
					sb.append("AND T1.INTENTION_STATE >= 10 ");
				}
				if (intentionVO.getIntentionOprType() == 1) {
					sb.append("AND T1.INTENTION_STATE = 1 ");
				}
				if (intentionVO.getIntentionOprType() == 2) {
					sb.append("AND T1.INTENTION_STATE = 10 ");
				}
				if (intentionVO.getIntentionOprType() == 3) {
					sb.append("AND T1.INTENTION_STATE >= 20 AND T1.INTENTION_STATE < 80 ");
				}
				if (intentionVO.getIntentionOprType() == 4) {
					sb.append("AND T1.INTENTION_STATE = 80 ");
				}
				if (intentionVO.getIntentionOprType() == 5) {
					sb.append("AND T1.INTENTION_STATE >= 1 ");
				}

			}
		}

	}

	public void searchIntentionCondition(Intention intention, StringBuilder sb, List<Object> params) {
		if (this.isString(intention.getCustomerMobile())) {
			sb.append("AND I.CUSTOMER_MOBILE = ? ");
			params.add(intention.getCustomerMobile());
		}
		if (this.isString(intention.getFollowerId())){
			sb.append("AND I.FOLLOWER_ID = ? ");
			params.add(intention.getFollowerId());
		}
		if (this.isString(intention.getMarketId())) {
			sb.append("AND I.MARKET_ID = ? ");
			params.add(intention.getMarketId());
		}

		if (intention.getIntentionState() != null) {
			sb.append("AND I.INTENTION_STATE = ? ");
			params.add(intention.getIntentionState());
		}
		if (this.isString(intention.getShopId())) {
			sb.append("AND I.SHOP_ID = ? ");
			params.add(intention.getShopId());
		}
		if (this.isString(intention.getCustomerShopId())) {
			sb.append("AND I.CUSTOMER_SHOP_ID = ? ");
			params.add(intention.getCustomerShopId());
		}
		if (intention.getIntentionType() != null) {
			sb.append("AND I.INTENTION_TYPE = ? ");
			params.add(intention.getIntentionType());
		}
		if (this.isString(intention.getIntentionId())) {
			sb.append("AND I.INTENTION_ID = ? ");
			params.add(intention.getIntentionId());
		}
		if (this.isString(intention.getIntentionId())) {
			sb.append("AND I.INTENTION_ID = ? ");
			params.add(intention.getIntentionId());
		}
	}


}
